/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP
DATE: 7/22/2022

PROJECT DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (This Syntax): 
Identify 
-Patients with 1 year observation, 
-clinics that are primary PC clinics for any patient

Jorge's steps: ASCEND.
	Patients
	1. Determine date of last encounter, first encounter
	2. Determine Index visit (first visit >=7/1/2016)
	3. Determine observation length, flag observation length >=365 days,
		limit to patients with last_encounter-first_encounter>=365 days
	4. determine primary clinic during observation period 
	

	Clinics
		List of primary_clinics
		Compare to ASCEND

INPUT DATA: 
SCRAP.SCRAP_potential_cohort_PC_enc

OUTPUT DATA:
SCRAP.CRC_patients
SCRAP.CVC_patients
SCRAP.BC_Patients
SCRAP.SCRAP_Clinics

SECTIONS:
	A.CRC and BC Patients
		1. Determine date of last PC encounter, first PC encounter
		2.Determine observation length, flag observation length >=365 days,
		3.Determine Index visit: last PC visit at age between 50 and 74 > 1 year before last encounter
		4. determine primary clinic during observation period 
			>>SCRAP.SCRAP_CRC_Patients
		5. Age range for BC same as CRC: if sex_r='F' then 
			>>SCRAP.SCRAP_BC_Patients


	B.CVC_Patients
		1. Determine date of last PC encounter, first PC encounter
		2.Determine observation length, flag observation length >=365 days,
		3.Determine Index visit: last PC visit at age between 23 and 64 > 1 year before last encounter
		4. determine primary clinic during observation period 
			>>SCRAP.SCRAP_CVC_Patients
	C. List of SCRAP Primary Care Clinics
		  >>SCRAP.SCRAP_Clinics

*****************************************************/
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";

/********************************************
	A.CRC and BC Patients
*********************************************/
/*Determine date of last encounter, first encounter*/;


proc sql;
create table bookends as
select raw_patid, ADVANCE_patid,sex_r,
	min(contact_date) as first_enc format date9.,
	max(contact_date) as last_enc format date9.
	from SCRAP.SCRAP_potential_cohort_PC_enc 
	where age_at_encounter_date between 50 and 74
	group by raw_patid, ADVANCE_patid, sex_r;


/*Determine observation length, flag observation length >=365 days,*/
/*limit to patients with last_encounter-first_encounter>=365 days*/
data patients;
set bookends;
length_obs=last_enc-first_enc;
if length_obs<365 then delete;
run;


/*Determine Index visit: last PC visit at age between 50 and 74 > 1 year before last encounter*/
proc sql;
create table CRC_index_visit as
select p.raw_patid,p.advance_patid,p.sex_r, p.last_enc,
max(e.contact_date) as index_visit format date9.
from patients p left join SCRAP.SCRAP_potential_cohort_PC_enc e
on p.raw_patid=e.raw_patid
where p.last_enc-e.contact_date>=365 and 
age_at_encounter_date between 50 and 74
group by p.raw_patid, p.advance_patid, p.sex_r, p.last_enc;


/*		4. determine primary clinic during observation period 
			>>SCRAP.SCRAP_CRC_Patients*/
proc sql;
create table pat_clinics as
	select e.raw_patid, e.advance_patid, e.sex_r,e.delivery_site_id,
	count(e.encounterid) as encs
	from CRC_index_visit i left join SCRAP.SCRAP_potential_cohort_PC_enc e
	on i.raw_patid=e.raw_patid and 
	e.contact_date between i.index_visit and i.last_enc
	group by e.raw_patid, e.advance_patid, e.sex_r, delivery_site_id
	order by e.raw_patid, e.advance_patid, calculated encs, delivery_site_id;

data primary_clinic;
set pat_clinics;
by raw_patid;
if last.raw_patid then output;
run;

proc sql;
create table SCRAP.SCRAP_CRC_Patients as
select c.raw_patid, c.advance_patid, c.sex_r, c.delivery_site_id,
c.encs as PC_encs_at_primary_clinic,
i.index_visit,
i.last_enc ,
yrdif(index_visit, last_enc,'age') as years_observation 
from 
primary_clinic c left join CRC_index_visit i
on c.raw_patid=i.raw_patid;

data scrap.scrap_crc_patients;
set scrap.scrap_crc_patients;
if PC_encs_at_primary_clinic=1 then delete;
if years_observation<1 then years_observation=1;
run;


/*Age range for BC same as CRC: if sex_r='F' then 
			>>SCRAP.SCRAP_BC_Patients*/
data scrap.scrap_bc_patients;
set scrap.scrap_crc_patients;
where sex_r='F';
run;

/*Lists of primary_clinics 	
			>>SCRAP.SCRAP_CRC_clinics
			>>SCRAP.SCRAP_BC_clinics*/
proc sql;
create table scrap.SCRAP_CRC_clinics as
select distinct delivery_site_id,
count(distinct raw_patid) as CRC_patients 
from scrap.scrap_crc_patients
group by delivery_site_id;

proc sql;	
create table scrap.SCRAP_BC_clinics as
select distinct delivery_site_id,
count(distinct raw_patid) as BC_patients 
from scrap.scrap_bc_patients
group by delivery_site_id;

/********************************************************************
	B.CVC_Patients
**********************************************************************/

/*Determine date of last PC encounter, first PC encounter at age 23-64*/
proc sql;
create table bookends as
select raw_patid,advance_patid,sex_r,
	min(contact_date) as first_enc format date9.,
	max(contact_date) as last_enc format date9.
	from SCRAP.SCRAP_potential_cohort_PC_enc 
	where sex_r='F'  and age_at_encounter_date between 23 and 64
	group by raw_patid,advance_patid, sex_r;


/*Determine observation length, flag observation length >=365 days,*/
data patients;
set bookends;
length_obs=last_enc-first_enc;
if length_obs<365 then delete;
run;


/*Determine Index visit: last PC visit at age between 23 and 64 > 1 year before last encounter*/
proc sql;
create table CVC_index_visit as
select p.raw_patid,p.advance_patid,p.sex_r, p.last_enc,
max(e.contact_date) as index_visit format date9.
from patients p left join SCRAP.SCRAP_potential_cohort_PC_enc e
on p.raw_patid=e.raw_patid
where p.last_enc-e.contact_date>=365 and 
age_at_encounter_date between 23 and 64
group by p.raw_patid,p.advance_patid, p.sex_r, p.last_enc;


/*determine primary clinic during observation period */
/*			>>SCRAP.SCRAP_CVC_Patients*/
proc sql;
create table pat_clinics as
	select e.raw_patid, e.advance_patid, e.sex_r,e.delivery_site_id,
	count(e.encounterid) as encs
	from CVC_index_visit i left join SCRAP.SCRAP_potential_cohort_PC_enc e
	on i.raw_patid=e.raw_patid and 
	e.contact_date between i.index_visit and i.last_enc
	group by e.raw_patid, e.advance_patid, e.sex_r, delivery_site_id
	order by e.raw_patid, e.advance_patid, calculated encs, delivery_site_id;

data primary_clinic;
set pat_clinics;
by raw_patid;
if last.raw_patid then output;
run;

proc sql;
create table SCRAP.SCRAP_CVC_Patients as
select c.raw_patid, c.advance_patid,c.sex_r, c.delivery_site_id,
c.encs as PC_encs_at_primary_clinic,
i.index_visit,
i.last_enc ,
yrdif(index_visit, last_enc,'age') as years_observation 
from 
primary_clinic c left join CVC_index_visit i
on c.raw_patid=i.raw_patid;

data scrap.scrap_cvc_patients;
set scrap.scrap_cvc_patients;
if PC_encs_at_primary_clinic=1 then delete;
if years_observation<1 then years_observation=1;
run;*70,649;


/*List of primary_clinics 	*/
/*			>>SCRAP.SCRAP_CVC_facilities	*/
proc sql;
create table scrap.SCRAP_CVC_clinics as
select distinct delivery_site_id,
count(distinct raw_patid) as CVC_patients 
from scrap.scrap_cvc_patients
group by delivery_site_id;

/*Combine clinics*/
data SCRAP_clinics;
merge scrap.scrap_crc_clinics scrap.scrap_bc_clinics scrap.scrap_cvc_clinics;
by delivery_site_id;
run;

proc sql;
create table scrap.SCRAP_clinics as
select 
c.delivery_site_id,
d.city,
d.delivery_site_name,
d.state_abbr,
d.site_type,
d.trctfips_2010,
d.primary_ruca_2010,
z.primary_ruca_code,
c.crc_patients,
c.cvc_patients,
c.bc_patients
from scrap_clinics c left join ochin.advance_care_delivery_sites d
on c.delivery_site_id=d.delivery_site_id
left join res_lib.Urban_Rural_by_Tract_RUCA2010 z
on z.state_county_tract_FIPS=d.trctFIPS_2010;

data scrap.scrap_clinics;
set scrap.scrap_clinics;
if primary_ruca_code='99' then primary_ruca_code='10';
run;

